package saeildamil.ch21.db;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javax.swing.JOptionPane;

import saeildamil.ch21.db.GoodsInfoDTO;


public class JdbcEx01 {
	public static void main(String[] args) {
		Connection con=null;

		try {
			con = dbCon();
			JOptionPane.showMessageDialog(null, "데이터베이스 연결 성공");
			selectDao(con);
			insertDao(con);
		} finally {
			try {
				con.close();
			}catch(Exception e){}
		}
	}

	private static void insertDao(Connection con) {
		Statement stmt = null;
		String name = JOptionPane.showInputDialog("상품이름 입력").trim();
		int price = Integer.parseInt(JOptionPane.showInputDialog("가격 입력").trim());
		String maker = JOptionPane.showInputDialog("제조사 입력").trim();
		
		String sql = "insert into goodsinfo (name, price, maker) values ('"+name+"'," + price + ", '" + maker + "')";
		JOptionPane.showMessageDialog(null, sql);
	}

	private static void selectDao(Connection con) {
		Statement stmt = null;
		ResultSet rs=null;
		ArrayList<GoodsInfoDTO> goodsList = new ArrayList<>();
		int code=0, price=0;
		String name=null, maker=null;
		GoodsInfoDTO obj = null;
		String sql;
		try {
			stmt = con.createStatement();
			int num = Integer.parseInt(JOptionPane.showInputDialog("1. 결과검색 2. 조건 검색"));
			if (num ==1){
				sql = "select code, name, price, maker from goodsinfo;";
			}else{
				String searchName = JOptionPane.showInputDialog("검색 하고자하는 이름을 입력");
				sql = "select code, name, price, maker from goodsinfo where name='"+searchName+"'";
			}
			rs = stmt.executeQuery(sql);
			while(rs.next()){
				code = rs.getInt(1);
				name = rs.getString("name");
				price = rs.getInt("price");
				maker = rs.getString(4);
				obj = new GoodsInfoDTO(code, name, price, maker);
				goodsList.add(obj);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		for(GoodsInfoDTO goods:goodsList){
			System.out.println(goods);
		}		
	}

	private static Connection dbCon(){
		Connection conn = null;
		BufferedReader bis=null;
		File file = new File("dbinfo.con");
		try {
			bis = new BufferedReader(new FileReader(file));
			String className = bis.readLine();
			String url = bis.readLine();
			String user= bis.readLine();
			String password = bis.readLine();
			Class.forName(className);
			conn = DriverManager.getConnection(url, user, password);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try {bis.close();}catch(Exception e){}
		}
		return conn;
		
	}
}